﻿using Dapper;
using Dapper.Contrib.Extensions;
using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Threading.Tasks;

namespace SXFramWork.Core
{
    /// <summary>
    /// 数据仓储抽象
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    public abstract class RepositoryAbstract<TEntity>:IRepository<TEntity> where TEntity : class, new()
    {

        protected ISql<TEntity> FromSql { get; set; }

        /// <summary>
        /// 数据库上下文
        /// </summary>
        protected readonly IDbContext DbContext;

        protected RepositoryAbstract(IDbContext dBContext)
        {
            DbContext = dBContext;
            FromSql = new Sql<TEntity>(DbContext.GetConnectionStr(),DbContext);
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="unitOfWork">工作单元</param>
        /// <returns></returns>
        public virtual Task<bool> DeleteAsync(TEntity entity, IUnitOfWork unitOfWork=null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return DbContext.NewConnection(tran).DeleteAsync(entity,tran);
        }
        /// <summary>
        /// 删除多条或一条记录
        /// </summary>
        /// <param name="id">值</param>
        /// <param name="tableName">表名</param>
        /// <param name="key">主键名</param>
        /// <param name="unitOfWork">工作单元</param>
        /// <returns></returns>
        public virtual async Task<int> DeleteAsync(dynamic id,string tableName, string key = "Id", IUnitOfWork unitOfWork=null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            var count = await DbContext.NewConnection(tran).ExecuteScalarAsync("select  count(1)   from   syscolumns   where   id=object_id(@TableName)   and   name=@Field", new { TableName = tableName, Field = key },tran);
            if (count.To_Int() <= 0)
                throw new Exception("数据表：" + tableName + "不存在字段" + key);
            var res = await DbContext.NewConnection(tran).ExecuteAsync("delete from " + tableName + " where " + key + "=@Val", new { Val = id },tran);
            return  res ;
        }
        /// <summary>
        /// 删除多条记录
        /// </summary>
        /// <param name="ids">值</param>
        /// <param name="tableName">表名</param>
        /// <param name="key">主键名</param>
        /// <param name="unitOfWork">工作单元</param>
        /// <returns></returns>
        public virtual async Task<int> DeleteManyAsync(List<string> ids, string tableName, string key = "Id", IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            var count = await DbContext.NewConnection(tran).ExecuteScalarAsync("select  count(1)   from   syscolumns   where   id=object_id(@TableName)   and   name=@Field", new { TableName = tableName, Field = key });
            if (count.To_Int() <= 0)
                throw new Exception("数据表：" + tableName + "不存在字段" + key);
            var res = await DbContext.NewConnection(tran).ExecuteAsync("delete from " + tableName + " where " + key + " in @ids", new { ids = ids }, tran);
            return res;
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="id"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public virtual Task<TEntity> GetAsync(string id,IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return DbContext.NewConnection(tran).GetAsync<TEntity>(id, tran);
        }
        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="unitOfWork"></param>
        /// <returns>主键</returns>
        public virtual Task<string> InsertAsync(TEntity entity, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return  DbContext.NewConnection(tran).InsertAsync(entity, tran);
        }
        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="list"></param>
        /// <param name="unitOfWork"></param>
        /// <returns>成功的条数</returns>
        public virtual async Task<int> InsertAsync(IEnumerable<TEntity> list, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            var res= await DbContext.NewConnection(tran).InsertAsync(list, tran);
            return res.To_Int();
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public virtual Task<bool> UpdateAsync(TEntity entity, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return DbContext.NewConnection(tran).UpdateAsync(entity, tran);
        }
        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="id">值</param>
        /// <param name="tableName">表名</param>
        /// <param name="key">主键名</param>
        /// <param name="unitOfWork">工作单元</param>
        /// <returns></returns>
        public virtual async Task<bool> ExistsAsync(dynamic id, string tableName, string key = "Id", IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            var count= await DbContext.NewConnection(tran).ExecuteScalarAsync("select  count(1)   from   syscolumns   where   id=object_id(@TableName)   and   name=@Field",new {TableName=tableName,Field=key },tran);
            if (count.To_Int() <= 0)
                throw new Exception("数据表：" + tableName + "不存在字段" + key);
            var res =await DbContext.NewConnection(tran).ExecuteScalarAsync("select count(1) from " + tableName + " where " + key + "=@Val", new { Val =id }, tran);
            return res.To_Int() > 0 ? true : false;
        }

        /// <summary>
        /// 新增或更新【主键有值则为更新】
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="tableName">表名</param>
        /// <param name="key">主键名</param>
        /// <param name="unitOfWork">工作单元</param>
        /// <returns>主键值</returns>
        public virtual async Task<string> CreateOrUpdate(TEntity entity, string tableName, string key = "Id",IUnitOfWork unitOfWork=null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            string result = "";
            var sql = "";
            //获取表结构
            List<string> column_List = new List<string>();
            var flag = 0;//标识是更新还是新增
            var type = entity.GetType();

            //主键属性
            var property = type.GetProperty(key);
            
            if (property == null)
            {
                //新增
                flag = 0;
            }
            else
            {
                //更新
                flag = 1;
            }
            sql = "select STUFF((select  ','+ COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME=" + tableName + " for xml path('')),1,1,'')";
            //获得列名
            var res = await DbContext.NewConnection(tran).ExecuteScalarAsync(sql);
            column_List=res.To_String().ToLower().Split(',').ToList();
            List<string> updateColumns = new List<string>();
            List<string> insertColumns = new List<string>();

            List<object> Values = new List<object>();
            int an_i = 0;
            foreach (string s in column_List)
            {
                if (s == key) continue;//排除主键
                if (s.IndexOf('=') != -1) continue;
                if (type.GetProperty(s) == null) continue;//如果实体没有这个字段

                if (type.GetProperty(s).GetValue(entity,null) == null)//处理空
                {
                    updateColumns.Add("[" + s + "]=Null");
                    insertColumns.Add("[" + s + "]");
                    Values.Add(null);
                }
                else
                {
                    updateColumns.Add("[" + s + "]=@" + an_i);
                    insertColumns.Add("[" + s + "]");
                    Values.Add(type.GetProperty(s).GetValue(entity, null));
                    an_i++;

                }

            }
            insertColumns.Add("[" + key.ToLower() + "]");
            //构造匿名对象
            var expando = new System.Dynamic.ExpandoObject();
            if (flag == 0)
            {
                var id = GuidHelper.NewSequentialGuid(SequentialGuidType.SequentialAtEnd).To_String();
                //新增时候添加主键值
                Values.Add(id);
                an_i = 0;
                List<string> ans = new List<string>();
                for (int i = 0; i < Values.Count; i++)
                {
                    if (Values[i] == null)
                        ans.Add("Null");
                    else
                    {
                        ans.Add("@" + an_i);
                        an_i++;
                    }

                }
                ans.Add("@" + (an_i+1).To_String());
                string InsertSql = "insert into [" + tableName + "] (" + string.Join(",", insertColumns) + ") values(" + string.Join(",", ans) + ");select @@identity";
                Values.RemoveAll(n => n == null);

                if (Values.Count > 1) //防止插入空数据
                {
                  
                   for (int i = 0; i < Values.Count; i++)
                   {
                        AddProperty(expando, "@" + i, Values[i]);
                    }

                   var identity=  await DbContext.NewConnection(tran).ExecuteScalarAsync(InsertSql, expando as object);
                    result= identity.To_String();
                }

            }
            else
            {
                string updateSql = "update [" + tableName + "] set " + string.Join(",", updateColumns);
                Values.RemoveAll(n => n == null);
                if (Values.Count > 1) //防止插入空数据
                {
                    for (int i = 0; i < Values.Count; i++)
                    {
                        AddProperty(expando, "@" + i, Values[i]);
                    }
                    updateSql += " where [" + key + "]=" + type.GetProperty(key).GetValue(entity, null);
                    var ss= await DbContext.NewConnection(tran).ExecuteAsync(updateSql,expando as object);
                    result= type.GetProperty(key).GetValue(entity, null).To_String();
                }
            }
            return result;
        }

        /// <summary>
        /// 匿名对象添加属性
        /// </summary>
        /// <param name="expando"></param>
        /// <param name="propertyName"></param>
        /// <param name="propertyValue"></param>
        private static void AddProperty(ExpandoObject expando, string propertyName, object propertyValue)
        {
            //扩展对象支持IDictionary，因此我们可以像这样扩展它
            var expandoDict = expando as IDictionary<string, object>;
            if (expandoDict.ContainsKey(propertyName)) //是否包含该属性
                expandoDict[propertyName] = propertyValue;
            else
                expandoDict.Add(propertyName, propertyValue);
        }
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="list"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public virtual Task<bool> DeleteAsync(IEnumerable<TEntity> list, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return DbContext.NewConnection(tran).DeleteAsync(list, tran);
        }
        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="list"></param>
        /// <param name="unitOfWork"></param>
        /// <returns></returns>
        public virtual Task<bool> UpdateAsync(IEnumerable<TEntity> list, IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return DbContext.NewConnection(tran).UpdateAsync(list, tran);
        }

        public virtual Task<IEnumerable<TEntity>> GetAllAsync(IUnitOfWork unitOfWork = null)
        {
            var uow = DbContext.GetCurrentUow();
            if (uow?.Transaction != null)
                unitOfWork = uow;
            var tran = unitOfWork?.Transaction;
            return DbContext.NewConnection(tran).GetAllAsync<TEntity>(tran);
        }
    }
    
}
